// db/database.js
const Database = require('better-sqlite3');

// 创建数据库连接（db 文件自动生成）
const db = new Database('redbook.db');

// 初始化表结构（只执行一次）
function initDB() {
	
	db.prepare(`
		CREATE TABLE IF NOT EXISTS system_setting (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			setting TEXT UNIQUE,
			value TEXT
		)
	`).run()
	
	// 手机场景-mobile_scene
	db.prepare(`
		CREATE TABLE IF NOT EXISTS mobile_scene (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			user_code TEXT,
			title TEXT,
			desc TEXT,
			width INTEGER,
			height INTEGER
		)
	`).run()
  
	db.prepare(`
		CREATE TABLE IF NOT EXISTS blood_bar (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			scene_id INTEGER,
			title TEXT,
			x INTEGER,
			y INTEGER,
			scale REAL,
			opacity REAL,
			countdown INTEGER,
			current_countdown INTEGER,
			countdown_status INTEGER,
			share_countdown INTEGER,
			use_user_icon INTEGER,
			auto_bind INTEGER,
			style TEXT,
			rounds INTEGER,
			show INTEGER
		)
	`).run()
	
	db.prepare(`
		CREATE TABLE IF NOT EXISTS user_table (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			show INTEGER,
			user_code TEXT,
			name TEXT,
			header TEXT,
			nick_name TEXT,
			gift_icon TEXT,
			gift_source_id TEXT,
			gift_name TEXT
		)
	`).run()
	
	db.prepare(`
		CREATE TABLE IF NOT EXISTS gifts_story (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			rev_user_id INTEGER,
			user_id TEXT,
			gift_source_id TEXT,
			gift_icon TEXT,
			gift_name TEXT,
			gift_coins INTEGER,
			create_time INTEGER,
			room_id TEXT,
			user_code TEXT
		)
	`).run()
	
	db.prepare(`
		CREATE TABLE IF NOT EXISTS blood_bar_user_temp (
			u_id INTEGER,
			cpt_id INTEGER,
			position INTEGER,
			coins INTEGER,
			win_count INTEGER,
			user_code TEXT
		)
	`).run()
	
	db.prepare(`
		CREATE TABLE IF NOT EXISTS gifts (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			scene_id INTEGER,
			title TEXT,
			x INTEGER,
			y INTEGER,
			scale REAL,
			space INTEGER,
			opacity REAL,
			auto_bind INTEGER,
			style TEXT,
			show INTEGER
		)
	`).run()
	
	db.prepare(`
		CREATE TABLE IF NOT EXISTS gifts_user_temp (
			u_id INTEGER,
			cpt_id INTEGER,
			position INTEGER,
			coins INTEGER,
			user_code TEXT
		)
	`).run()
	
	db.prepare(`
		CREATE TABLE IF NOT EXISTS rank (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			scene_id INTEGER,
			title TEXT,
			x INTEGER,
			y INTEGER,
			scale REAL,
			opacity REAL,
			countdown INTEGER,
			current_countdown INTEGER,
			countdown_status INTEGER,
			share_countdown INTEGER,
			max_show_num INTEGER,
			style TEXT,
			show INTEGER
		)
	`).run()
	
	db.prepare(`
		CREATE TABLE IF NOT EXISTS console_story (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			user_code TEXT,
			type TEXT,
			desc TEXT,
			create_time INTEGER
		)
	`).run()
	
	db.prepare(`
		CREATE TABLE IF NOT EXISTS fans (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			user_code TEXT,
			user_id TEXT UNIQUE,
			nick_name TEXT,
			avatar TEXT,
			coins INTEGER,
			bind_uid INTEGER
		)
	`).run()
	
	db.prepare(`
		CREATE TABLE IF NOT EXISTS user_temp_mapping (
			u_id INTEGER,
			cpt_id INTEGER,
			cpt_t_name TEXT,
			gift_id INTEGER,
			coins INTEGER,
			user_code TEXT
		)
	`).run()
}


/**
 * 返回礼物历史数据与组件对应关系
 * @param {Object} gift_id 礼物历史表的id
 * @param {Object} user_code 
 */
function selectUserTempMapping(gift_id, user_code) {
	const sql = `
		SELECT * FROM user_temp_mapping
		WHERE 
			gift_id = '${gift_id}'
		AND
			user_code = '${user_code}'
	`
	const row = db.prepare(sql).get()
	return row
}

/**
 * 删除票数与组件对应关系
 */
function delUser_temp_mapping(gift_id) {
	const del = db.prepare(`
	    DELETE FROM user_temp_mapping WHERE gift_id = ${gift_id}
	`)
	del.run()
}

/**
 * 情况所有日志信息
 */
function delConsoleStory() {
	const del = db.prepare(`
	    DELETE FROM console_story
	`)
	del.run()
}

/**
 * getNowFans 获取本场礼物记录
 * @param {Object} user_code
 * @param {Object} room_id
 */
function getGifts_story(room_id, user_code) {
	const sql = `
	    SELECT 
			g.id,
	        g.user_id,
	        COALESCE(f.nick_name, g.user_id) AS sender_name,
	        g.gift_source_id,
	        g.gift_icon,
	        g.gift_name,
	        g.gift_coins,
	        g.create_time,
			CASE WHEN g.rev_user_id = 0 THEN 0 ELSE u.id END AS receiver_id,
			CASE WHEN g.rev_user_id = 0 THEN '' ELSE u.header END AS receiver_header,
			CASE WHEN g.rev_user_id = 0 THEN '' ELSE u.nick_name END AS receiver_nick_name
	    FROM 
	        gifts_story g
	    LEFT JOIN 
	        user_table u ON g.rev_user_id = u.id AND g.rev_user_id != 0
	    LEFT JOIN 
	        fans f ON g.user_id = f.user_id
	    WHERE 
	        g.room_id = ?
	        AND g.user_code = ?
	    ORDER BY 
	        g.id DESC
	`
	const select = db.prepare(sql)
	return select.all(room_id, user_code)
}

/**
 * getNowFans 获取本次粉丝送礼排行
 * @param {Object} user_code
 * @param {Object} room_id
 */
function getNowFans(user_code, room_id) {
	const sql = `
	    SELECT 
	        f.user_id AS fan_user_id,
	        f.nick_name AS fan_nickname,
	        f.avatar AS fan_avatar,
	        f.bind_uid AS bind_anchor_id, 
	        u.nick_name AS anchor_nick_name,  
	        u.header AS anchor_avatar,      
	        SUM(g.gift_coins) AS coins,
	        COUNT(g.id) AS gift_count
	    FROM 
	        gifts_story g
	    JOIN 
	        fans f ON g.user_id = f.user_id
	    LEFT JOIN 
	        user_table u ON f.bind_uid = u.id  -- 关联主播信息
	    WHERE 
	        g.room_id = ?
	        AND g.user_code = ?
	    GROUP BY 
	        f.user_id
	    ORDER BY 
	        coins DESC
	`
	// console.log(sql)
	const select = db.prepare(sql)
	return select.all(room_id, user_code)
}

/**
 * 插入系统数据
 * 参数见 ‘db/system_setting’
 * @param {Object} datas
 */
function insertSystem_setting(datas) {
	for (let data of datas) {
		const insert = db.prepare(`
		    INSERT INTO system_setting (setting, value)
		    VALUES (?, ?)
		    ON CONFLICT(setting) DO UPDATE SET
		        value = excluded.value
		`)
		const result = insert.run(
		    data.setting,
		    data.value
		)
	}
}

/**
 * 插入粉丝数据
 * 参数见 ‘db/粉丝数据fans’
 * @param {Object} data
 */
function insertFans(data) {
	const insert = db.prepare(`
	    INSERT INTO fans (user_code, user_id, nick_name, avatar, coins, bind_uid)
	    VALUES (?, ?, ?, ?, ?, ?)
	    ON CONFLICT(user_id) DO UPDATE SET
	        user_code = excluded.user_code,
	        nick_name = excluded.nick_name,
	        avatar = excluded.avatar
	`)
	const result = insert.run(
	    data.user_code,
	    data.user_id,
	    data.nick_name,
	    data.avatar,
	    data.coins,
		data.bind_uid
	)
	console.log('插入成功，ID 为:', result.lastInsertRowid);
	return result.lastInsertRowid;
}

/**
 * 更新粉丝票数
 * 参数见 ‘db/粉丝数据fans’
 * @param {Object} data
 */
function updateFansCoins(data) {
	const sql = `
	    UPDATE fans 
	    SET coins = coins + ? 
	    WHERE user_id = ? AND user_code = ?
	`
	const result = db.prepare(sql).run(
		data.coins,
	    data.user_id,
		data.user_code
	)
}

/**
 * 更新；减去组件票数
 * 参数见 ‘user_temp所有表’
 * @param {Object} data
 */
function cutUserTempCoins(data) {
	const sql = `
	    UPDATE ${data.cpt_t_name} 
	    SET coins = coins - ? 
	    WHERE u_id = ? AND cpt_id = ? AND user_code = ?
	`
	const result = db.prepare(sql).run(
		data.coins,
	    data.u_id,
		data.cpt_id,
		data.user_code
	)
}

/**
 * 更新粉丝绑定关系
 * 参数见 ‘db/粉丝数据fans’
 * @param {Object} data
 */
function updateFansBind(data) {
	const sql = `
	    UPDATE fans 
	    SET bind_uid = ?
	    WHERE user_id = ? AND user_code = ?
	`
	const result = db.prepare(sql).run(
		data.bind_uid,
	    data.user_id,
		data.user_code
	)
}

function selectFans(user_id, user_code) {
	const sql = `
		SELECT * FROM fans
		WHERE 
			user_id = '${user_id}'
		AND
			user_code = '${user_code}'
	`
	const row = db.prepare(sql).get()
	return row
}

function getAllFans(user_code) {
	const sql = `
		SELECT * FROM fans
		WHERE 
			user_code = '${user_code}'
		ORDER BY coins DESC LIMIT 100
	`
	const row = db.prepare(sql).all()
	return row
}


/**
 * 插入总信息台信息
 * 参数见 ‘db/总信息台日志-console_story.xlsx’
 * @param {Object} data
 */
function insertConsole_story(data) {
	// console.log(data)
	const insert = db.prepare(`
		INSERT INTO console_story (user_code, type, desc, create_time)
		VALUES (?, ?, ?, ?)
	`)
	const result = insert.run(data.user_code, data.type, data.desc, data.create_time)
	console.log('插入成功，ID 为:', result.lastInsertRowid)
	return result.lastInsertRowid
}

/**
 * 获取总信息台信息
 * @param {Object} user_code 登录code
 */
function getConsole_story(user_code, type) {
	let rows = []
	
	if (type) {
		rows = db.prepare('SELECT * FROM console_story WHERE user_code = ? AND type = ? ORDER BY id DESC LIMIT 100').all(user_code, type)
	} else {
		rows = db.prepare('SELECT * FROM console_story WHERE user_code = ? ORDER BY id DESC LIMIT 100').all(user_code)
	}
	
	
	rows.sort((a, b) => {
		return a.id - b.id
	})
	
	return rows
}

/**
 * 插入场景
 * 参数见 ‘db/手机场景-mobile_scene.xlsx’
 * @param {Object} data
 */
function insertMobleScene(data) {
	const insert = db.prepare(`
		INSERT INTO mobile_scene (user_code, title, desc, width, height)
		VALUES (?, ?, ?, ?, ?)
	`)
	const result = insert.run(data.user_code, data.title, data.desc, data.width, data.height)
	console.log('插入成功，ID 为:', result.lastInsertRowid)
	return result.lastInsertRowid
}

function randomNumStr12() {
  return Math.floor(Math.random() * 1e12).toString().padStart(12, '0');
}

/**
 * 获取场景
 * 返回类容说明见 ‘db/手机场景-mobile_scene.xlsx’
 * @param {Object} user_code 登录用户码
 */
function getMobleScene(user_code) {
	const rows = db.prepare('SELECT * FROM mobile_scene WHERE user_code = ?').all(user_code)
	let cts
	for (let row of rows) {
		row.allComponents = []
		cts = db.prepare('SELECT * FROM blood_bar WHERE scene_id = ?').all(row.id)
		for (let ct of cts) {
			ct.temp_id = randomNumStr12()
			ct.table_name = 'blood_bar'
			row.allComponents.push(ct)
		}
		updateTableTemp({
			table_name: 'blood_bar_user_temp',
			coins: 0,
			position: 0
		})
		cts = db.prepare('SELECT * FROM gifts WHERE scene_id = ?').all(row.id)
		for (let ct of cts) {
			ct.temp_id = randomNumStr12()
			ct.table_name = 'gifts'
			row.allComponents.push(ct)
		}
		updateTableTemp({
			table_name: 'gifts_user_temp',
			coins: 0
		})
		cts = db.prepare('SELECT * FROM rank WHERE scene_id = ?').all(row.id)
		for (let ct of cts) {
			ct.temp_id = randomNumStr12()
			ct.table_name = 'rank'
			row.allComponents.push(ct)
		}
		// updateTableTemp({
		// 	table_name: 'blood_bar_user_temp',
		// 	coins: 0
		// })
	}
	return rows
}

/**
 * 更新场景数据
 * @param {Object} data 场景数据
 */
function saveMobleScene(data) {
	const update = db.prepare(`
		UPDATE mobile_scene
		SET user_code = ?, title = ?, desc = ?, width = ?, height = ?
		WHERE id = ?
	`)
	const result = update.run(
		data.user_code,
		data.title,
		data.desc,
		data.width,
		data.height,
		data.id
	);
}

/**
 * @param {Object} tableName 表名
 * @param {Object} field where条件
 * @param {Object} value where条件值
 */
function deleteByField(tableName, field, value) {
	const stmt = db.prepare(`DELETE FROM ${tableName} WHERE ${field} = ?`);
	stmt.run(value);
}

/**
 * 插入组件到场景
 * 参数见 ‘db/手机场景-data.table_name.xlsx’
 * @param {Number} scene_id
 * @param {Object} data
 */
function insertComponents(scene_id, insertComponents) {

	deleteByField('blood_bar', 'scene_id', scene_id)
	deleteByField('gifts', 'scene_id', scene_id)
	deleteByField('rank', 'scene_id', scene_id)

	if (!insertComponents) return
	for (let data of insertComponents) {
		const fields = [];
		const values = [];
		const placeholders = [];
		for (const [key, value] of Object.entries(data)) {
		  if (key !== 'table_name' && key != 'temp_id' && value !== null) {
		    fields.push(key);
		    values.push(value);
		    placeholders.push('?'); // 使用占位符防止 SQL 注入
		  }
		}
		// 2. 生成 SQL 语句
		const tableName = data.table_name;
		const sql = `
		  INSERT INTO ${tableName} (${fields.join(', ')})
		  VALUES (${placeholders.join(', ')})
		`;
		// 3. 执行 SQL（假设使用 SQLite）
		const insert = db.prepare(sql);
		const result = insert.run(...values); // 传入动态值
		data.id = result.lastInsertRowid
	}
}

/**
 * 更新组件表
 * @param {Object} cpt
 */
function updateCptTable(userCptTemp) {
	if (!userCptTemp) return;

	const updates = [];
	const values = [];

	// 过滤掉 table_name、u_id、cpt_id，以及 null 值
	for (const [key, value] of Object.entries(userCptTemp)) {
		if (key !== 'table_name' && key !== 'id' && key != 'temp_id' && value !== null) {
			updates.push(`${key} = ?`);
			values.push(value);
		}
	}

	// WHERE 条件的值添加到 values 最后
	values.push(userCptTemp.id);

	const tableName = userCptTemp.table_name;
	const sql = `
		UPDATE ${tableName}
		SET ${updates.join(', ')}
		WHERE id = ?
	`;

	const update = db.prepare(sql);
	const result = update.run(...values); // 执行更新操作
}

/**
 * 插入临时用户和组件的数据
 * @param {Object} userCptTemp
 */
function insertTableTemp(userCptTemp, returnId) {
	if (!userCptTemp) return
	const fields = [];
	const values = [];
	const placeholders = [];
	for (const [key, value] of Object.entries(userCptTemp)) {
		if (key !== 'table_name' && value !== null) {
			fields.push(key);
			values.push(value);
			placeholders.push('?'); // 使用占位符防止 SQL 注入
		}
	}
	// 2. 生成 SQL 语句
	const tableName = userCptTemp.table_name;
	const sql = `
		INSERT INTO ${tableName} (${fields.join(', ')})
		VALUES (${placeholders.join(', ')})
	`;
	// console.log(sql)
	// console.log(values)
	// 3. 执行 SQL（假设使用 SQLite）
	const insert = db.prepare(sql);
	const result = insert.run(...values); // 传入动态值
	if (returnId) {
		return result.lastInsertRowid
	}
}

/**
 * 更新临时用户和组件的数据
 * @param {Object} userCptTemp
 */
function updateTableTemp(userCptTemp) {
	if (!userCptTemp) return;

	const updates = [];
	const values = [];

	// 过滤掉 table_name、u_id、cpt_id，以及 null 值
	for (const [key, value] of Object.entries(userCptTemp)) {
		if (key !== 'table_name' && key !== 'u_id' && key !== 'cpt_id' && value !== null) {
			updates.push(`${key} = ?`);
			values.push(value);
		}
	}

	// 检查必要的 WHERE 条件是否存在
	if (!userCptTemp.u_id && !userCptTemp.cpt_id) {
		const tableName = userCptTemp.table_name;
		const sql = `
			UPDATE ${tableName}
			SET ${updates.join(', ')}
		`;
		const update = db.prepare(sql);
		const result = update.run(...values); // 执行更新操作
		return
	}

	// WHERE 条件的值添加到 values 最后
	values.push(userCptTemp.u_id, userCptTemp.cpt_id);

	const tableName = userCptTemp.table_name;
	const sql = `
		UPDATE ${tableName}
		SET ${updates.join(', ')}
		WHERE u_id = ? AND cpt_id = ?
	`;
	const update = db.prepare(sql);
	const result = update.run(...values); // 执行更新操作
}

/**
 * 插入主播
 * 参数见 ‘db/主播表-user_table.xlsx’
 * @param {Object} data
 */
function insertUser(data) {
	const insert = db.prepare(`
		INSERT INTO user_table (user_code, show, name, header, nick_name, gift_icon, gift_source_id, gift_name)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?)
	`)
	const result = insert.run(data.user_code, data.show, data.name, data.header, data.nick_name, data.gift_icon, data.gift_source_id, data.gift_name)
	console.log('插入成功，ID 为:', result.lastInsertRowid)
	return result.lastInsertRowid
}

/**
 * 查询主播礼物总票数
 * @param {Object} user_id 主播id
 */
function totalBind_user_idGiftCoins(rev_user_id) {
	const result  = db.prepare(`SELECT SUM(gift_coins) AS total_gift_coins
		FROM gifts_story
		WHERE rev_user_id = ?`
	).get(rev_user_id)
	return result.total_gift_coins ? result.total_gift_coins : 0
}

/**
 * 查询主播本场直播总票数
 * @param {Object} user_id 主播id
 */
function nowBind_user_idGiftCoins(rev_user_id, room_id) {
	const result  = db.prepare(`SELECT SUM(gift_coins) AS total_gift_coins
		FROM gifts_story
		WHERE rev_user_id = ?
			AND room_id = ?
		`
	).get([rev_user_id, room_id])
	return result.total_gift_coins ? result.total_gift_coins : 0
}

/**
 * 获取主播列表
 * @param {Object} user_code 登录code
 */
function getUserList(user_code) {
	const rows = db.prepare('SELECT * FROM user_table WHERE user_code = ? ORDER BY id DESC').all(user_code)
	const room_id = selectSystem_setting('room_id').value
	for (let row of rows) {
		const total_gift_coins = totalBind_user_idGiftCoins(row.id)
		row.total_gift_coins = total_gift_coins
		if (room_id == 0 || room_id == null) {
			row.now_gift_coins = 0
		} else {
			row.now_gift_coins = nowBind_user_idGiftCoins(row.id, room_id)
		}
	}
	return rows
}

/**
 * 根据专属礼物查询主播
 * @param {Object} user_code 登录code
 */
function getUserInfoWithGiftId(user_code, giftId) {
	const row = db.prepare('SELECT * FROM user_table WHERE user_code = ? AND gift_source_id = ? AND show = 1').get(user_code, giftId)
	return row
}

/**
 * 删除主播
 * @param {Object} userId 主播id
 */
function delUser(userId) {
	deleteByField('user_table', 'id', userId)
	deleteByField('gifts_user_temp', 'u_id', userId)
	deleteByField('blood_bar_user_temp', 'u_id', userId)
}

/**
 * 更新场景数据
 * @param {Object} data 场景数据
 */
function saveUser(data) {
	const update = db.prepare(`
		UPDATE user_table
		SET 
		    show = ?,
		    name = ?,
		    header = ?,
		    nick_name = ?,
		    gift_icon = ?,
		    gift_source_id = ?,
		    gift_name = ?
		WHERE id = ?
	`)
	const result = update.run(
		data.show,
		data.name,
		data.header,
		data.nick_name,
		data.gift_icon,
		data.gift_source_id,
		data.gift_name,
		data.id
	)
}

/**
 * 查询用户临时关系表
 * @param {string} table_user_temp 表名
 * @param {string} u_id 用户id
 * @param {string} cpt_id 组件id
 * @param {string} user_code 登录用户
 */
function selectTableTemp(table_user_temp, u_id, cpt_id, user_code) {
	const sql = `
		SELECT * FROM ${table_user_temp} 
		WHERE 
			u_id = ${u_id} 
		AND
			cpt_id = ${cpt_id}
		AND 
			user_code = ?
	`
	const row = db.prepare(sql).get(user_code)
	return row
}

/**
 * 查询系统设置
 */
function selectSystem_setting(setting) {
	const sql = `
		SELECT * FROM system_setting 
		WHERE 
			setting = '${setting}'
	`
	const row = db.prepare(sql).get()
	return row
}

/**
 * 更新系统数据
 */
function updateSystem_setting(setting, value) {
	
	const sql = `
	    UPDATE system_setting
	    SET 
	        value = ?
	    WHERE setting = ?
	`
	const result = db.prepare(sql).run(
		value,
		setting
	)
}

/**
 * 更新礼物接受者
 */
function updateGiftStoryRever(gift_id, rev_user_id) {
	
	const sql = `
	    UPDATE gifts_story
	    SET 
	        rev_user_id = ?
	    WHERE id = ?
	`
	const result = db.prepare(sql).run(
		rev_user_id,
		gift_id
	)
}

// 导出方法
module.exports = {
  initDB,
  randomNumStr12, // 12位随机字符串
  insertMobleScene, // 插入场景
  getMobleScene, 	// 获取场景
  saveMobleScene, 	// 保存场景
  insertComponents,  // 场景中插入组件
  insertUser, // 插入主播
  getUserList, // 获取主播列表
  delUser, // 删除主播
  saveUser, // 保存主播
  selectTableTemp, // 查询用户临时关系表
  insertTableTemp, // 用户临时关系表插入数据
  updateTableTemp, // 用户临时关系表更新数据
  updateCptTable, // 更新组件表
  insertConsole_story, // 插入总信息台信息
  getConsole_story, // 获取总新系统数据
  insertFans, // 插入粉丝数据
  selectFans, // 查询粉丝数据
  updateFansCoins, // 更新粉丝票数
  getAllFans, // 获取所有粉丝，以分数进行排行
  insertSystem_setting, // 插入系统数据
  updateSystem_setting, // 更新系统数据
  selectSystem_setting, // 查询系统数据
  getUserInfoWithGiftId, // 根据礼物id查询主播
  updateFansBind, // 更新粉丝绑定关系
  nowBind_user_idGiftCoins, // 查询主播本场票数
  totalBind_user_idGiftCoins, // 查询主播总票数
  delConsoleStory, // 清空控制面板历史信息
  getNowFans, // 获取本次粉丝送礼排行
  getGifts_story, // 获取本场礼物记录
  cutUserTempCoins, // 减去用户与组件临时表的票数
  selectUserTempMapping, // 返回礼物历史数据与组件对应关系
  delUser_temp_mapping, // 删除票数与组件对应关系
  updateGiftStoryRever, // 更新礼物接受者
};
